/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ijournalimporter;

import ijournalimporter.util.DBHelper;
import ijournalimporter.util.ExcelUtil;
import ijournalimporter.util.Util;
import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import javax.swing.JProgressBar;
import javax.swing.JTextArea;

/**
 *
 * @author eric
 */
public class ArticleDataImporter {

    private DBHelper helper;
    private File excel;
    private JTextArea output;
    private JProgressBar progressBar;
    private boolean ok = true;
    private HashMap journals = null;
    private HashMap articles = null;
    private static final int SQL_NULL = java.sql.Types.NULL;
    private static final String now = Util.getTodayWithTime();

    public ArticleDataImporter(DBHelper helper, File excel, JTextArea output, JProgressBar progressBar) {
        this.helper = helper;
        this.excel = excel;
        this.output = output;
        this.progressBar = progressBar;

        helper.setAutocommit(false);
    }

    public String doImport() throws Exception {
        // read excel
        ArrayList<ArrayList> rows = ExcelUtil.readExcelToArray(excel, null);
        if (rows == null || rows.isEmpty()) {
            return "讀取Excel檔失敗或無資料！";
        }

        // setup progress bar
        progressBar.setIndeterminate(false);
        progressBar.setMaximum(rows.size());
        progressBar.setStringPainted(true);

        // backup 
        backup();

        //  validate
        this.readCurrentJournals();
        this.readCurrentArticles();

        for (int i = 0; i < rows.size(); i++) {
            ArrayList record = rows.get(i);
            validate(record, i + 1);
        }

        if (!ok) {
            return "資料檢查錯誤，停止匯入！";
        } else {
            this.println("資料驗證OK");
            this.println("開始匯入，共" + rows.size() + "筆");
        }

        PreparedStatement ps = helper.getPreparedStatement("article", 16);
        for (int i = 0; i < rows.size(); i++) {
            ArrayList record = rows.get(i);
            insert(ps, record, i);
            progressBar.setValue(i);
            Util.delayMS(300);
        }
        ps.executeBatch(); // insert

        println("\r\n匯入文章成功：" + rows.size() + " 筆");

        return null; // insert OK
    }

    private void backup() {
        helper.excuteUpdate("CREATE TABLE article_" + now + " SELECT * FROM article");
        println("backup \"article\" to \"article_" + now + "\"......OK");
    }

    private void println(Object msg) {
        StringBuffer sb = new StringBuffer(output.getText());
        sb.append(msg);
        sb.append("\r\n");
        output.setText(sb.toString());
    }

    private void validate(ArrayList<String> record, int no) {
        // tjcd
        String tjcd = record.get(0);
        if (Util.isBlank(tjcd)) {
            this.println("ERROR: 第" + no + "筆資料的TJCD為空值");
            ok = false;
        }

        if (articles.containsKey(tjcd)) {
            this.println("ERROR: TJCD = " + tjcd + " TJCD重複");
            ok = false;
        } else {
            articles.put(tjcd, record.get(2));
        }

        // journal
        String journalName = record.get(1);
        if (Util.isBlank(journalName)) {
            this.println("ERROR: TJCD = " + tjcd + " 期刊名稱不能為空值");
            ok = false;
        } else {
            if (!journals.containsKey(journalName)) {
                this.println("ERROR: TJCD = " + tjcd + " 無此期刊名稱：" + journalName);
                ok = false;
            }
        }

        // name
        if (Util.isBlank(record.get(2))) {
            this.println("ERROR: TJCD = " + tjcd + " 文章名稱不能為空值");
            ok = false;
        }

        // 出版年
        if (Util.isBlank(record.get(6))) {
            this.println("ERROR: TJCD = " + tjcd + " 出版年不能為空值");
            ok = false;
        }
    }

    private void readCurrentJournals() throws Exception {
        journals = new HashMap();
        ResultSet rs = helper.executeQuery("SELECT name,id FROM journal");
        while (rs.next()) {
            journals.put(rs.getString("name"), rs.getString("id"));
        }
    }

    private void readCurrentArticles() throws Exception {
        articles = new HashMap();
        ResultSet rs = helper.executeQuery("SELECT tjcdCode,name FROM article");
        while (rs.next()) {
            articles.put(rs.getString("tjcdCode"), rs.getString("name"));
        }
    }

    private void insert(PreparedStatement ps, ArrayList<String> record, int i) throws SQLException {
        // tjcd
        String tjcd = this.getString(record, 0);
        ps.setString(1, tjcd);

        // fkJournalID
        String journalName = this.getString(record, 1);
        ps.setString(2, (String) journals.get(journalName));

        // name
        String name = this.getString(record, 2);
        ps.setString(3, name);

        // author 
        String author = this.getString(record, 3);
        ps.setString(4, author);

        //volume
        String volume = this.getString(record, 4);
        ps.setString(5, volume);

        //issue
        String issue = this.getString(record, 5);
        ps.setString(6, issue);

        // pubishYear 
        String pubishYear = this.getString(record, 6);
        ps.setString(7, pubishYear);

        //authorized
        String authorized = this.getString(record, 7);
        if (authorized.equals("是")) {
            ps.setString(8, "on");
        } else {
            ps.setNull(8, SQL_NULL);
        }

        // authorizationLetter
        String authorizationLetter = this.getString(record, 8);
        if (authorizationLetter.equals("是")) {
            ps.setString(9, "on");
        } else {
            ps.setNull(9, SQL_NULL);
        }

        // digitalFormat
        String digitalFormat = this.getString(record, 9);
        if (digitalFormat.equals("是")) {
            ps.setString(10, "on");
        } else {
            ps.setNull(10, SQL_NULL);
        }

        // currJournal
        String currJournal = this.getString(record, 10);
        if (currJournal.equals("現刊")) {
            ps.setString(11, "on");
        } else {
            ps.setNull(11, SQL_NULL);
        }

        //note
        String note = this.getString(record, 11);
        ps.setString(12, note);

        // cid
        ps.setString(13, "IMPORTER");

        // ctime
        ps.setString(14, now);

        // uid
        ps.setNull(15, SQL_NULL);

        // utime
        ps.setNull(16, SQL_NULL);

        ps.addBatch();

        println("TJCD = " + tjcd + " 匯入成功");
    }

    private String getString(ArrayList<String> record, int col) {
        String s = null;
        try {
            s = record.get(col);
        } catch (Exception e) {
            return "";
        }

        if (Util.isNotBlank(s)) {
            s = s.trim();
        } else {
            s = "";
        }
        return s;
    }
}
